package com.iplant.ams.serviceImpl.dao.dms.scada;

import com.iplant.ams.service.basicenum.dms.scada.DMSDataClass;
import com.iplant.ams.service.po.dms.DMSDeviceLedger;
import com.iplant.ams.service.po.dms.scada.para.DMSDeviceRealParameter;
import com.iplant.base.po.BasePO;
import com.iplant.base.po.OutResult;
import com.iplant.base.po.enums.DBEnumType;
import com.iplant.base.po.enums.DBSchemas;
import com.iplant.base.po.enums.ExceptionTypes;
import com.iplant.base.utils.cache.CacheUtils;
import com.iplant.base.utils.general.CalendarTool;
import com.iplant.base.utils.general.StringUtils;
import com.iplant.base.utils.sql.BaseDAO;
import com.iplant.basic.service.po.bms.BMSEmployee;
import lombok.extern.slf4j.Slf4j;

import java.util.*;
import java.util.stream.Collectors;


@Slf4j
public class DMSDeviceRealParameterDAO extends BaseDAO {

    private static DMSDeviceRealParameterDAO Instance = null;

    private DMSDeviceRealParameterDAO() {
        super("dms_device_parametervalue", DBEnumType.MySQL, DBSchemas.AMS);
    }

    public static DMSDeviceRealParameterDAO getInstance() {
        if (Instance == null)
            Instance = new DMSDeviceRealParameterDAO();
        return Instance;
    }

    private List<DMSDeviceRealParameter> DMS_SelectDeviceRealParameterList(BMSEmployee wLoginUser, List<Integer> wID, String wCode,
                                                                           String wParameterName, List<String> wVariableName,

                                                                           int wAreaID, int wDeviceID, String wAssetNo, int wDataType, int wDataClass, int wPositionID,
                                                                           OutResult<Integer> wErrorCode) {
        return this.DMS_SelectDeviceRealParameterList(wLoginUser, wID, wCode,
                wParameterName, wVariableName,
                wAreaID, StringUtils.parseListArgs(wDeviceID), wAssetNo, wDataType, wDataClass, wPositionID,
                wErrorCode);
    }

    public List<DMSDeviceRealParameter> DMS_SelectDeviceRealParameterListByDeviceID(BMSEmployee wLoginUser, List<Integer> wDeviceID, OutResult<Integer> wErrorCode) {
        return this.DMS_SelectDeviceRealParameterList(wLoginUser, null, "",
                "", null,
                -1, wDeviceID, "", -1, -1, -1,
                wErrorCode);
    }

    private List<DMSDeviceRealParameter> DMS_SelectDeviceRealParameterList(BMSEmployee wLoginUser, List<Integer> wID, String wCode,
                                                                           String wParameterName, List<String> wVariableName,

                                                                           int wAreaID, List<Integer> wDeviceID, String wAssetNo, int wDataType, int wDataClass, int wPositionID,
                                                                           OutResult<Integer> wErrorCode) {

        List<DMSDeviceRealParameter> wResult = new ArrayList<DMSDeviceRealParameter>();
        try {

            wErrorCode.set(0);


            if (wID == null)
                wID = new ArrayList<Integer>();
            wID.removeIf(p -> p <= 0);

            if (wDeviceID == null)
                wDeviceID = new ArrayList<Integer>();
            wDeviceID.removeIf(p -> p <= 0);

            if (wCode == null)
                wCode = "";
            if (wParameterName == null)
                wParameterName = "";
            if (wVariableName == null)
                wVariableName = new ArrayList<String>();

            wVariableName.removeIf(p -> StringUtils.isEmpty(p));

            if (wAssetNo == null)
                wAssetNo = "";

            String wSQL = StringUtils.Format(
                    "SELECT p.*,t.ID as ParameterID, t.Name as ParameterName,t.Code as ParameterCode,t.DeviceID,t1.AssetNo,"
                            + " t.VariableName,t.DataType,t.DataClass,t.KeyChar,t.AuxiliaryChar,t.ValueLeft,t.ValueRight,"
                            + " t.ParameterDesc,t.PositionID,t7.Name as PositionName "
                            + " FROM {0}.dms_device_parameter t "
                            + " inner join {0}.dms_device_ledger t1 on t.DeviceID=t1.ID  "
                            + " left join {0}.dms_device_parametervalue p on p.ParameterNo=t.Code and p.DeviceID=t1.AssetNo "
                            + " left join {0}.dms_position t7 on  t.PositionID=t7.ID  "
                            + "  WHERE  ( :wID ='''' or t.ID IN ( ''{1}'' ) )  "
                            + " and ( :wCode ='''' or t.Code  = :wCode)  "
                            + " and ( :wName ='''' or t.Name   like  :wName )  "
                            + " and ( :wDeviceID <= 0 or t.DeviceID  in ( ''{3}'' ))   "
                            + " and ( :wPositionID <= 0 or t.PositionID  = :wPositionID)   "
                            + " and ( :wAreaID <= 0 or t1.WorkAreaID  = :wAreaID)   "
                            + " and ( :wAssetNo ='''' or t1.AssetNo  = :wAssetNo)  "
                            + " and ( :wVariableName ='''' or t.VariableName in ( ''{2}'' ) )  "
                            + " and ( :wDataType <= 0 or t.DataType  = :wDataType)   "
                            + " and ( :wDataClass <= 0 or t.DataClass  = :wDataClass)   "
                            + " and  t.Active  = 1   group by t.ID  ;"
                    , getDBName(), StringUtils.Join("','", wID), StringUtils.Join("','", wVariableName), StringUtils.Join("','", wDeviceID));
            wSQL = this.DMLChange(wSQL);
            Map<String, Object> wParamMap = new HashMap<>();
            wParamMap.put("wID", StringUtils.Join(",", wID));
            wParamMap.put("wName", StringUtils.isEmpty(wParameterName) ? wParameterName : "%" + wParameterName + "%");
            wParamMap.put("wCode", wCode);
            wParamMap.put("wDeviceID", StringUtils.Join("','", wDeviceID));
            wParamMap.put("wPositionID", wPositionID);
            wParamMap.put("wAreaID", wAreaID);
            wParamMap.put("wAssetNo", wAssetNo);
            wParamMap.put("wVariableName", StringUtils.Join(",", wVariableName));
            wParamMap.put("wDataType", wDataType);
            wParamMap.put("wDataClass", wDataClass);

            List<Map<String, Object>> wQueryResult = this.queryForList(wSQL, wParamMap);

            if (wQueryResult == null || wQueryResult.size() == 0)
                return wResult;

            List<DMSDeviceLedger> wDMSDeviceLedgerList = (List<DMSDeviceLedger>) CacheUtils.GetCacheList(DMSDeviceLedger.class);
            Map<Integer, DMSDeviceLedger> wDMSDeviceLedgerMap = wDMSDeviceLedgerList.stream().collect(Collectors.toMap(p -> p.ID, p -> p));

            for (Map<String, Object> wReader : wQueryResult) {
                DMSDeviceRealParameter wDeviceRealParameter = new DMSDeviceRealParameter();

                wDeviceRealParameter.ID = StringUtils.parseInt(wReader.containsKey("ID") ? wReader.get("ID") : "");

                wDeviceRealParameter.PositionID = StringUtils.parseInt(wReader.get("PositionID"));
                wDeviceRealParameter.PositionName = StringUtils.parseString(wReader.get("PositionName"));
                wDeviceRealParameter.ParameterID = StringUtils.parseInt(wReader.get("ParameterID"));
                wDeviceRealParameter.ParameterCode = StringUtils.parseString(wReader.get("ParameterCode"));
                wDeviceRealParameter.ParameterName = StringUtils.parseString(wReader.get("ParameterName"));
                wDeviceRealParameter.VariableName = StringUtils.parseString(wReader.get("VariableName"));
                wDeviceRealParameter.DeviceID = StringUtils.parseInt(wReader.get("DeviceID"));
                if (wDMSDeviceLedgerMap.containsKey(wDeviceRealParameter.DeviceID)) {
                    wDeviceRealParameter.DeviceName = wDMSDeviceLedgerMap.get(wDeviceRealParameter.DeviceID).Name;
                    wDeviceRealParameter.DeviceNo = wDMSDeviceLedgerMap.get(wDeviceRealParameter.DeviceID).Code;
                }
                wDeviceRealParameter.AssetNo = StringUtils.parseString(wReader.get("AssetNo"));
                wDeviceRealParameter.DataType = StringUtils.parseInt(wReader.get("DataType"));
                wDeviceRealParameter.DataClass = StringUtils.parseInt(wReader.get("DataClass"));
                wDeviceRealParameter.KeyChar = StringUtils.parseString(wReader.get("KeyChar"));
                wDeviceRealParameter.AuxiliaryChar = StringUtils.parseString(wReader.get("AuxiliaryChar"));
                wDeviceRealParameter.ValueLeft = StringUtils.parseDouble(wReader.get("ValueLeft"));
                wDeviceRealParameter.ValueRight = StringUtils.parseDouble(wReader.get("ValueRight"));
                wDeviceRealParameter.ParameterDesc = StringUtils.parseString(wReader.get("ParameterDesc"));
                wDeviceRealParameter.ParameterValue = StringUtils.parseString(wReader.get("ParameterValue"));
                wDeviceRealParameter.UpdateTime = StringUtils.parseCalendar(wReader.get("UpdateDate"));
                wResult.add(wDeviceRealParameter);
            }

        } catch (Exception e) {
            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("DMS_SelectDeviceRealParameterList", e);
        }
        return wResult;
    }


    public List<DMSDeviceRealParameter> DMS_SelectDeviceRealParameterList(BMSEmployee wLoginUser, List<Integer> wDeviceIDList, List<String> wAssetNoList,
                                                                          OutResult<Integer> wErrorCode) {

        List<DMSDeviceRealParameter> wResult = new ArrayList<DMSDeviceRealParameter>();
        try {

            if (wDeviceIDList == null)
                wDeviceIDList = new ArrayList<Integer>();

            wDeviceIDList.removeIf(p -> p <= 0);

            if (wAssetNoList == null)
                wAssetNoList = new ArrayList<String>();

            wAssetNoList.removeIf(p -> StringUtils.isEmpty(p));

            wErrorCode.set(0);
            String wInstance = getDBName();

            String wSQL = StringUtils.Format(
                    "SELECT !isnull(p.ParameterValue) as ID, p.*,t.ID as ParameterID, t.Name as ParameterName,t.Code as ParameterCode, t.DeviceID,t1.AssetNo,"
                            + " t.VariableName,t.DataType,t.DataClass,t.KeyChar,t.AuxiliaryChar,"
                            + " t.ParameterDesc,t1.Code as  DeviceNo,t1.Name as DeviceName"
                            + " FROM {0}.dms_device_parameter t "
                            + " inner join {0}.dms_device_ledger t1 on t.DeviceID=t1.ID  "
                            + " left join {0}.dms_device_parametervalue p  on p.ParameterNo=t.Code and p.DeviceID=t1.AssetNo "
                            + " WHERE 1=1  and  t.Active=1  "
                            + " and ( :wAssetNo ='''' or t1.AssetNo  in  (''{1}''))  "
                            + " and ( :wDeviceID ='''' or t1.ID  in  ({2}))  group by t.ID   "
                    , wInstance, wAssetNoList.size() > 0 ? StringUtils.Join("','", wAssetNoList) : "0",
                    wDeviceIDList.size() > 0 ? StringUtils.Join(",", wDeviceIDList) : "0");
            wSQL = this.DMLChange(wSQL);
            Map<String, Object> wParamMap = new HashMap<>();
            wParamMap.put("wDeviceID", StringUtils.Join(",", wDeviceIDList));
            wParamMap.put("wAssetNo", StringUtils.Join("','", wAssetNoList));

            List<Map<String, Object>> wQueryResult = this.queryForList(wSQL, wParamMap);


            // wReader\[\"(\w+)\"\]
            for (Map<String, Object> wReader : wQueryResult) {
                DMSDeviceRealParameter wDeviceRealParameter = new DMSDeviceRealParameter();

                wDeviceRealParameter.ID = StringUtils.parseInt(wReader.containsKey("ID") ? wReader.get("ID") : "");

                wDeviceRealParameter.ParameterID = StringUtils.parseInt(wReader.get("ParameterID"));
                wDeviceRealParameter.ParameterCode = StringUtils.parseString(wReader.get("ParameterCode"));
                wDeviceRealParameter.ParameterName = StringUtils.parseString(wReader.get("ParameterName"));
                wDeviceRealParameter.VariableName = StringUtils.parseString(wReader.get("VariableName"));
                wDeviceRealParameter.DeviceID = StringUtils.parseInt(wReader.get("DeviceID"));
                wDeviceRealParameter.DeviceName = StringUtils.parseString(wReader.get("DeviceName"));
                wDeviceRealParameter.DeviceNo = StringUtils.parseString(wReader.get("DeviceNo"));
                wDeviceRealParameter.AssetNo = StringUtils.parseString(wReader.get("AssetNo"));

                wDeviceRealParameter.DataType = StringUtils.parseInt(wReader.get("DataType"));
                wDeviceRealParameter.IsChange = StringUtils.parseInt(wReader.get("IsChange"));

                wDeviceRealParameter.DataClass = StringUtils.parseInt(wReader.get("DataClass"));

                wDeviceRealParameter.KeyChar = StringUtils.parseString(wReader.get("KeyChar"));
                wDeviceRealParameter.AuxiliaryChar = StringUtils.parseString(wReader.get("AuxiliaryChar"));
                wDeviceRealParameter.ParameterDesc = StringUtils.parseString(wReader.get("ParameterDesc"));

                wDeviceRealParameter.ParameterValue = StringUtils.parseString(wReader.get("ParameterValue"));
                wDeviceRealParameter.UpdateTime = StringUtils.parseCalendar(wReader.get("UpdateDate"));
                wResult.add(wDeviceRealParameter);
            }

        } catch (Exception e) {
            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("DMS_SelectDeviceRealParameterList", e);
        }
        return wResult;
    }


    public List<DMSDeviceRealParameter> DMS_SelectDeviceRealParameterList(BMSEmployee wLoginUser, String wParameterName, List<String> wVariableName,

                                                                          int wAreaID, int wDeviceID, String wAssetNo, int wDataType, int wDataClass, int wPositionID,
                                                                          OutResult<Integer> wErrorCode) {
        return this.DMS_SelectDeviceRealParameterList(wLoginUser, null, "", wParameterName, wVariableName,

                wAreaID, wDeviceID, wAssetNo, wDataType, wDataClass, wPositionID,
                wErrorCode);
    }

    public List<DMSDeviceRealParameter> DMS_SelectDeviceRealParameterList(BMSEmployee wLoginUser, String wVariableName,
                                                                          int wDataClass, OutResult<Integer> wErrorCode) {
        return this.DMS_SelectDeviceRealParameterList(wLoginUser, null, "", "", StringUtils.parseListArgs(wVariableName),

                -1, -1, "", -1, wDataClass, -1,
                wErrorCode);
    }


    public DMSDeviceRealParameter DMS_SelectDeviceRealParameter(BMSEmployee wLoginUser, int wID, String wCode,
                                                                OutResult<Integer> wErrorCode) {
        DMSDeviceRealParameter wResult = new DMSDeviceRealParameter();
        try {
            List<DMSDeviceRealParameter> wDMSDeviceRealParameterList = null;
            if (wID > 0) {
                List<Integer> wIDList = new ArrayList<Integer>();
                wIDList.add(wID);
                wDMSDeviceRealParameterList = this.DMS_SelectDeviceRealParameterList(wLoginUser, wIDList, "", "",
                        null, -1, -1, "", -1, -1, -1, wErrorCode);
            } else if (StringUtils.isNotEmpty(wCode)) {
                wDMSDeviceRealParameterList = this.DMS_SelectDeviceRealParameterList(wLoginUser, null, wCode, "",
                        null, -1, -1, "", -1, -1, -1, wErrorCode);
            } else {
                return wResult;
            }
            if (wDMSDeviceRealParameterList != null && wDMSDeviceRealParameterList.size() > 0)
                wResult = wDMSDeviceRealParameterList.get(0);

        } catch (Exception e) {
            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("DMS_SelectDeviceRealParameter", e);
        }
        return wResult;
    }

    public List<DMSDeviceRealParameter> DMS_SelectDeviceRealParameterList(BMSEmployee wLoginUser, List<Integer> wIDList,
                                                                          OutResult<Integer> wErrorCode) {
        List<DMSDeviceRealParameter> wResult = new ArrayList<DMSDeviceRealParameter>();
        try {
            if (wIDList == null || wIDList.size() < 1)
                return wResult;
            Calendar wBaseTime = CalendarTool.getBasicDate();
            List<Integer> wSelectList = new ArrayList<Integer>();
            for (int i = 0; i < wIDList.size(); i++) {
                wSelectList.add(wIDList.get(i));
                if (i % 25 == 0) {
                    wResult.addAll(this.DMS_SelectDeviceRealParameterList(wLoginUser, wSelectList, "", "",
                            null, -1, -1, "", -1, -1, -1, wErrorCode));

                    wSelectList.clear();
                }
                if (i == wIDList.size() - 1) {
                    if (wSelectList.size() > 0)
                        wResult.addAll(this.DMS_SelectDeviceRealParameterList(wLoginUser, wSelectList, "", "",
                                null, -1, -1, "", -1, -1, -1, wErrorCode));
                    break;
                }
            }
        } catch (Exception e) {
            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("DMS_SelectDeviceRealParameterList", e);
        }
        return wResult;
    }


    public String DMS_UpdateDeviceRealParameter(int wID, String wAssetNo, String wParameterCode, String wParameterValue) {

        if (StringUtils.isEmpty(wAssetNo) || StringUtils.isEmpty(wParameterCode))
            return "";
        if (wParameterValue == null)
            wParameterValue = "";

        if (wID > 0) {

            return StringUtils.Format(" Update  {0}.dms_device_parametervalue  set  UpdateDate =now() ,IsChange=1," +
                            " ChangeDate=now(),ParameterValue=''{1}'' " +
                            " where  DeviceID=''{2}'' and ParameterNo =''{3}'' ", getDBName(),
                    wParameterValue, wAssetNo, wParameterCode);

        } else {
            return StringUtils.Format(" Insert into {0}.dms_device_parametervalue (DeviceID,ParameterNo,ChangeDate,UpdateDate,ParameterValue) " +
                            "values ({1},''{2}'',now(),now(),''{3}'');", getDBName(), wAssetNo, wParameterCode,
                    wParameterValue);

        }
    }


    public String DMS_UpdateDeviceRealParameter(DMSDeviceRealParameter wDMSDeviceRealParameter) {

        if (wDMSDeviceRealParameter == null || StringUtils.isEmpty(wDMSDeviceRealParameter.AssetNo) || StringUtils.isEmpty(wDMSDeviceRealParameter.ParameterCode))
            return "";

        if (wDMSDeviceRealParameter.ID > 0) {

            return StringUtils.Format(" Update  {0}.dms_device_parametervalue  set  UpdateDate =now() ," +
                            " ChangeDate=str_to_date(''{1}'',''%Y-%m-%d %H:%i:%s''),ParameterValue=''{2}'' " +
                            " where  DeviceID=''{3}'' and ParameterNo =''{4}'' ", getDBName(), StringUtils.parseCalendarToString(wDMSDeviceRealParameter.UpdateTime, "yyyy-MM-dd HH:mm:ss"),
                    wDMSDeviceRealParameter.ParameterValue, wDMSDeviceRealParameter.AssetNo, wDMSDeviceRealParameter.ParameterCode);

        } else {
            return StringUtils.Format(" Insert into {0}.dms_device_parametervalue (DeviceID,ParameterNo,ChangeDate,UpdateDate,ParameterValue) " +
                            "values ({1},''{2}'',''{3}'',now(),''{4}'');", getDBName(), wDMSDeviceRealParameter.AssetNo, wDMSDeviceRealParameter.ParameterCode,
                    StringUtils.parseCalendarToString(wDMSDeviceRealParameter.UpdateTime, "yyyy-MM-dd HH:mm:ss"), wDMSDeviceRealParameter.ParameterValue);

        }
    }


    public List<Map<String, Object>> DMS_SelectProcessRecordParams(BMSEmployee wLoginUser, String wDeviceCode, Calendar wStartTime, Calendar wEndTime,
                                                                   OutResult<Integer> wErrorCode) {

        List<Map<String, Object>> wResult = new ArrayList<>();
        try {
            if (wEndTime == null || wStartTime == null || StringUtils.isEmpty(wDeviceCode))
                return wResult;
            String wTableName = DMSDataClass.Params.GetMysqlTablePrefixString() + "_" + wDeviceCode;

            String wInstance = getDBName();
            List<Map<String, Object>> wQueryTableResult = this.queryForList(
                    StringUtils.Format("SELECT * FROM information_schema.TABLES where table_name=''{1}'' and " +
                            "TABLE_SCHEMA =''{0}'';", wInstance, wTableName), null);
            if (wQueryTableResult.size() == 0)
                return wResult;
            Map<String, Object> wParams = new HashMap<>();
            wParams.put("wStartTime", wStartTime);
            wParams.put("wEndTime", wEndTime);
            List<Map<String, Object>> wQueryResult = this.queryForList(StringUtils.Format("select * From {0}.{1} " +
                    "where CreateTime between :wStartTime and :wEndTime ", wInstance, wTableName), wParams);

            wResult = wQueryResult;

        } catch (Exception e) {
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
            log.error("DMS_SelectProcessRecordParams", e);
        }
        return wResult;
    }


}
